package com.dhf.framework.system.service;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.MapUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Component;

import com.dhf.framework.base.mybatis.core.page.Pagination;
import com.dhf.framework.common.GenerateSqlUtil;
import com.dhf.framework.common.Params;
import com.dhf.framework.system.dao.CommonDao;

@Component
public class CommonService {
	
	private static Logger logger = LoggerFactory.getLogger(CommonService.class);

//	private static final String PRIMARYKEY="id";
	
//	private static final String PRI="PRI";
	
	private static final String PAGE="page";
	
	private static final String PAGESIZE="pageSize";
	
	@Autowired
	private SqlSessionFactory sqlSessionFactory;
	
	@Autowired
	private CommonDao commonDao;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public CommonDao getCommonDao(){
		SqlSession sqlSession = new SqlSessionTemplate(sqlSessionFactory);
		CommonDao commonDao= sqlSession.getMapper(CommonDao.class);
		sqlSession.close();
		return commonDao;
	}
	
	
	/**
	 * 
	* @Title: queryAllTables
	* @Description: 查询数据库所有表名以及表属性
	* @param     设定文件
	* @return List<Map<String, Object>>    返回类型
	 */
	public List<Map<String, Object>> queryAllTables(String tableSchema){
		String sql="select table_name,table_comment from information_schema.tables  where table_schema = '"+
				tableSchema+"'";
		return jdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 
	* @Title: queryTableFieldAttributes
	* @Description: 查询表字段以及字段注释
	* @param  tableSchema tableName
	* @return List<Map<String, Object>>    返回类型
	 */
	public List<Map<String, Object>> queryTableFieldAttributes(String tableSchema,String tableName){
		String sql="select  column_name,column_comment,column_key,DATA_TYPE  "
				+ " from information_schema.columns "
				+ " where table_schema  ='"+tableSchema
				+"' and table_name  = '"+tableName+"'";
		return jdbcTemplate.queryForList(sql);
	}
	
	
	/**
	 * 
	* @Title: queryTablePrimarykey
	* @Description: 查询表主键字段
	* @param  tableSchema 
	* @param  tableName
	* @return String  返回类型
	 */
	public String queryTablePrimarykey(String tableSchema,String tableName){
		String sql="SELECT k.column_name FROM "
				+ "information_schema.table_constraints t "
				+ "JOIN information_schema.key_column_usage k "
				+ "USING (constraint_name,table_schema,table_name) "
				+ "WHERE t.constraint_type='PRIMARY KEY' "
				+ "AND t.table_schema='"+tableSchema+"'"
				+ "AND t.table_name='"+tableName+"'";
		Map<String, Object> map= jdbcTemplate.queryForMap(sql);
		if(MapUtils.isNotEmpty(map)){
			return MapUtils.getString(map, "column_name");
		}
		return "";
	}
	
	
	/**
	 * 
	* @Title: queryTableFieldNames
	* @Description: 查询表字段集合
	* @param @param tableName
	* @return List<String>  
	 */
	public List<String> queryTableFieldNames(String tableName){
		List<String> reList=new ArrayList<String>();
		String sql = GenerateSqlUtil.generateSelectSql(tableName, null)+" limit 0";
		SqlRowSet sqlRowSet = this.jdbcTemplate.queryForRowSet(sql);  
		SqlRowSetMetaData sqlRsmd = sqlRowSet.getMetaData();  
		int columnCount = sqlRsmd.getColumnCount();  
		for (int i = 1; i <= columnCount; i++) {  
			reList.add(sqlRsmd.getColumnName(i));
		}
		return reList;
	}
	
	/**
	 * 
	* @Title: queryTableValues
	* @Description: 根据表明查询表数据集合
	* @param  tableName
	* @return List<Map<String,Object>>    返回类型
	 */
	public List<Map<String, Object>> queryTableValues(String tableName,Map<String,Object> map){
		List<String> list=this.queryTableFieldNames(tableName);
		Params params=new Params(); 
		params.setTableName(tableName);
		params.setTableFieldNames(list);
		params.setPage(MapUtils.getIntValue(map, PAGE));
		params.setPageSize(MapUtils.getIntValue(map,PAGESIZE));
		return this.findManyData(params);
	}
	
	/**
	 * 
	* @Title: filterFields
	* @Description: 过滤掉map不是表字段的值集
	* @param @param tableName
	* @param @param map
	* @param @return    设定文件
	* @return Map<String,Object>    返回类型
	* @throws
	 */
	public Map<String,Object> filterFields(String tableName,Map<String,Object> map){
		List<String> tableFieldNames=this.queryTableFieldNames(tableName);
		Iterator<String> it = map.keySet().iterator();
		while (it.hasNext()) {
			String key = it.next();
			if(!tableFieldNames.contains(key)){
				map.remove(key);
				logger.warn("表"+tableName+"没有字段:"+key);
			}
		}
		return map;
	}
	
	
	public Pagination<Map<String,Object>> queryPageList(Map<String,Object> map, Pagination<Map<String,Object>> pagination,String tableName) {
		logger.debug("查询翻页数据");
        long a = System.currentTimeMillis();
        Params params=new Params();
        params.setTableName(tableName);
		params.setMap(map);
		params.setPagination(pagination);
		int totalItemsCount = this.commonDao.getTotalItemsCount(params);
		List<String> tableFieldNames=this.queryTableFieldNames(tableName);
		params.setTableFieldNames(tableFieldNames);
        long b = System.currentTimeMillis();
        logger.debug("查询总数据数量耗时：" + (b - a) + "毫秒");
        List<Map<String,Object>> items = this.commonDao.paginationList(params);
        long c = System.currentTimeMillis();
        logger.debug("查询总数据集合耗时：" + (c - b) + "毫秒");
        pagination.setTotalItemsCount(totalItemsCount);
        pagination.setItems(items);
        return pagination;
	}
	
	/**
	 * 查找数量
	 * @author dhf
	 * @param map 查询的map条件
	 * @return
	 */
	public int getTotalItemsCount(Map<String,Object> map) {
		Params params=new Params();
		params.setMap(map);
		return commonDao.getTotalItemsCount(params);
	}
	
	/**
	 * 增加SQL操作
	 * @author dhf
	 * @param tableSchema 
	 * @param tableName 表名
	 * @param map  表字段值集
	 * @return
	 */
	public int add(String tableSchema,String tableName,Map<String,Object> map) {
		//map.put(this.queryTablePrimarykey(tableSchema,tableName), GuidUtil.newGuid());
		Params params=new Params(); 
		params.setTableName(tableName);
		params.setMap(map);
		return commonDao.add(params);
	}
	
	/**
	 * 更新SQL操作
	 * @author dhf
	 * @param tableSchema 
	 * @param tableName 表名
	 * @param map  表字段值集
	 * @return
	 */
	public int update(String tableSchema,String tableName,Map<String,Object> map) {
		String primarykey=this.queryTablePrimarykey(tableSchema,tableName);
		Params params=new Params(); 
		params.setTableName(tableName);
		params.setId(MapUtils.getString(map, primarykey));
		map.remove(primarykey);
		params.setMap(map);
		return commonDao.update(params);
	}
	
	/**
	 * 删除SQL操作
	 * @author dhf
	 * @param tableName 表名
	 * @param id 表主键id
	 * @return
	 */
	public int delete(String tableName,Map<String, Object> map) {
		Params params=new Params(); 
		params.setTableName(tableName);
		params.setMap(map);
		return commonDao.delete(params);
	}

	/**
	 * 查询单条语句
	 * @author dhf
	 * @param sql 操作的sql语句
	 * @return
	 */
	public Map<String, Object> findOneData(String tableName,Map<String,Object> map) {
		Params params=new Params(); 
		params.setTableName(tableName);
		params.setMap(map);
		params.setTableFieldNames(queryTableFieldNames(tableName));
		return commonDao.findOneData(params);
	}

	/**
	 * 查询多条语句
	 * @author dhf
	 * @param sql 操作的sql语句
	 * @return
	 */
	public List<Map<String, Object>> findManyData(Params params) {
		return commonDao.findManyData(params);
	}

	/**
	 * 查询单个
	 * @author dhf
	 * @param sql 查询的sql 语句
	 * @return
	 */
	public Object findOneValue(String sql) {
		return commonDao.findOneValue(sql);
	}
}
